/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ptit.student;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSetMetaData;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JOptionPane;

/**
 *
 * @author Admin
 */
public class Student {
    static Connection conn =null;
    static Statement stm;
    private  String student_id;
    private  String name;    
    private String classID,birth,createdDate;    
    private String gender;
    
    public Student()
    {
        
    }
    public Student(String student_id, String name,String birth,String createdDate,String classID,String gender)
    {
        this.student_id=student_id;
        this.name=name;
        this.birth=birth;
        this.createdDate=createdDate;
        this.classID=classID;
        this.gender = gender;
                
    }
    
    public  void connect(){
        try{
        String userConn ="root";
        String passConn = "tyty2629";
        String url = "jdbc:mysql://localhost:3306/mangementscore";
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        conn =  (Connection) DriverManager.getConnection(url, userConn, passConn);
        System.out.println("Da ket noi CSDL");
        stm = (Statement) conn.createStatement();
        }
        catch(Exception e)
        {
        System.out.println(e.toString());
         }
    }

    public  void disconnect() throws SQLException  {
        stm.close();
        conn.close();
    }
    
    public String showAll()
    {
       String allStudents = "";
        StringBuilder sb = new StringBuilder();
        try {
           
            ResultSet rs = stm.executeQuery("select * from student");
            ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
            sb.append("<table border>");
            sb.append("<tr>");
            addTitle(sb);


            while (rs.next()) {
                sb.append("<tr>");
                for (int i = 0; i < rsmd.getColumnCount(); i++) {
                    sb.append("<td>");
                    if(i+1!=6)               
                         sb.append(rs.getString(i + 1));                          
                    else{                                      
                          sb.append( selectClassName(rs.getString(i+1)));  
                          
                    } 
                       
                    
                   sb.append("</td>");
                }  
                 sb.append("<td><a href=\"Controller?requestAction=deleteStudent#studentID=")
                         .append(rs.getString(1)).append("\"><img src=\"Images/Delete32.png\"></a></td>");       
                 sb.append("<td><a href=\"EditStudent.jsp?studentID=")
                         .append(rs.getString(1)).append("#name=").append(rs.getString(2))
                        .append("#birth=").append(rs.getString(3)).append("#gender=").append(rs.getString(4))
                         .append("#createdDate=").append(rs.getString(5))
                         .append("#classLearning=").append( selectClassName(rs.getString(6)))
                         .append("\"><img src=\"Images/Student_edit32.png\"></a></td>");
                sb.append("</tr>");
            }

            sb.append("</table>");
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }
        allStudents = sb.toString();        
        return allStudents;
    }

    public void addStudent() throws SQLException {
     connect();
    String cmdInsert = "INSERT INTO student(student_id,name,birth,created_date,gender,class_id) VALUES ("
            + student_id+ "," 
            + "'" + name + "'" + "," 
            + "'" + birth + "'" +","
            + "'" + createdDate + "'"+","
            + "'" + gender + "'"+","
            + "'" + classID + "'" +");";
    
    stm.executeUpdate(cmdInsert);
    disconnect();
    }
    
    public void deleteStudent(String studentID) throws SQLException
    {
        connect();
        String cmdDelete = "Delete from student where student_id =  "+studentID;
        stm.executeUpdate(cmdDelete);
        disconnect();
    }

    public void editStudent() throws SQLException {
        connect();
        String cmdUpdate ="UPDATE `mangementscore`. `student` SET   `name`='"  + name     
                + "',birth='" + birth
                + "',gender='" + gender
                + "',created_date='" + createdDate
                + "',class_id='" + classID          
                +"'  where  student_id LIKE "+ student_id.trim()+";";
        stm.executeUpdate(cmdUpdate);
        disconnect();
    }
   public void addTitle(StringBuilder sb)
   {
            sb.append("<tr>");
            sb.append("<th>");
            sb.append("MSSV");
            sb.append("</th>");
            sb.append("<th>");
            sb.append("Họ và tên");
            sb.append("</th>");
            sb.append("<th>");
            sb.append("Ngày sinh");
            sb.append("</th>"); 
            sb.append("<th>");
            sb.append("Giới tính");
            sb.append("</th>");
            sb.append("<th>");
            sb.append("Niên khóa");
            sb.append("</th>");
            sb.append("<th>");
            sb.append("Lớp");
            sb.append("</th>");
           
            sb.append("<th>");
            sb.append("Xóa");
            sb.append("</th>");
            sb.append("<th>");
            sb.append("Sửa");
            sb.append("</th>");
            sb.append("</tr>");
   }
   public String searchStudent(String strSearch, boolean searchName) 
    {   
        String tbStudent = "";   //sinh vien duoc tim thay 
        StringBuilder sb = new StringBuilder();
        String cmdSelect="";
        if(searchName==true)  //tim theo ten
             cmdSelect = "select * from student where name = '" + strSearch+"';";
        else                //tim theo mssv
             cmdSelect = "select * from student where student_id =" + strSearch;
          try {
            ResultSet rs = stm.executeQuery(cmdSelect);
            ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
            sb.append("<table border>");
           
           addTitle(sb);
         
            
             while (rs.next()) {
                sb.append("<tr>");
                for (int i = 0; i < rsmd.getColumnCount(); i++) {
                    sb.append("<td>");
                    sb.append(rs.getString(i + 1));
                    sb.append("</td>");
                }  
                 sb.append("<td><a href=\"Controller?requestAction=deleteStudent#studentID=")
                         .append(rs.getString(1)).append("\"><img src=\"Images/Delete32.png\"></a></td>");       
                 sb.append("<td><a href=\"EditStudent.jsp?studentID=")
                         .append(rs.getString(1)).append("#name=").append(rs.getString(2))
                        .append("#birth=").append(rs.getString(3)).append("#gender=").append(rs.getString(4))
                         .append("#createdDate=").append(rs.getString(5))
                         .append("#classLearning=").append(rs.getString(6))
                         .append("\"><img src=\"Images/Student_edit32.png\"></a></td>");
                sb.append("</tr>");
            }
            sb.append("</table>");
         }
        catch (Exception e) {
           return "Không tìm thấy ";
        }
         tbStudent = sb.toString();        
        return tbStudent;
    }

    private String selectClassName(String classID) throws SQLException {
       Statement stmClassName = (Statement) conn.createStatement();
       String cmdClassName = "Select class_name from class where class_id = '"+classID+"';";                       
       ResultSet rsClass = stmClassName.executeQuery(cmdClassName);
        rsClass.next();              
            return  rsClass.getString(1);
    }
}